﻿using Dapper;
//using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using IDataReader = global::System.Data.Common.DbDataReader;

namespace LiteExpress.Core.DbClient
{
    public static class DbConectionExt
    {
        #region 扩展connection对象
        ///// <summary>
        ///// 扩展ExecuteReader方法
        ///// </summary>
        ///// <typeparam name="T">只支持自定义类</typeparam>
        ///// <param name="func">对每行的操作。返回false可将reader中途关闭</param>
        //public static bool ExecuteReader<T>(this DbConnection conn, string sql, Func<T, bool> func)
        //{
        //    return ExecuteReader<T>(conn, sql, null, func);
        //}

        ///// <summary>
        ///// 扩展ExecuteReader方法
        ///// </summary>
        ///// <typeparam name="T">只支持自定义类</typeparam>
        ///// <param name="param">如果sql中含有参数，则以json形式传入这个参数</param>
        ///// <param name="func">对每行的操作。返回false可将reader中途关闭</param>
        //public static bool ExecuteReader<T>(this DbConnection conn, string sql, object param, Func<T, bool> func)
        //{
        //    var type = typeof(T);
        //    using (var dr = conn.ExecuteReader(sql, param))
        //    {
        //        var deserializer = SqlMapper.GetTypeDeserializer(type, dr);

        //        T d;
        //        while (dr.Read() && !dr.IsClosed)
        //        {
        //            var val = deserializer(dr);
        //            if (val == null || val is T)
        //            {
        //                d = (T)val;
        //            }
        //            else
        //            {
        //                d = (T)Convert.ChangeType(val, type, System.Globalization.CultureInfo.InvariantCulture);
        //            }

        //            var success = func(d);
        //            if (!success)
        //            {
        //                dr.Close();
        //                return false;
        //            }
        //        }
        //        dr.Close();
        //    }
        //    return true;
        //}

        //public static bool ExecuteReader(this DbConnection conn, string sql, Func<DbDataReader, bool> func)
        //{
        //    return ExecuteReader(conn, sql, null, func);
        //}

        public static bool ExecuteReader(this DbConnection conn, string sql, object param, Func<DbDataReader, bool> func)
        {
            using (var dr = conn.ExecuteReader(sql, param))
            {
                while (dr.Read() && !dr.IsClosed)
                {
                    var success = func(dr);
                    if (!success)
                    {
                        dr.Close();
                        return false;
                    }
                }
                dr.Close();
            }
            return true;
        }

        /// <summary>
        /// 生成翻页sql脚本的format字符串，其中{0}为page，并且从1开始，{1}为size
        /// </summary>        
        /// <param name="from">sql的from部分，可使用Join</param>
        /// <param name="select">sql的select部分，如果使用了Join和别名，这里需要加上别名</param>
        /// <param name="where">sql的where部分，如果使用了Join和别名，这里需要加上别名</param>
        /// <param name="pkField">sql的每条记录的标识主键，如果使用了Join和别名，这里需要加上别名</param>
        /// <param name="orderBy">sql的orderby部分，如果使用了Join和别名，这里需要加上别名</param>
        /// <returns></returns>
        public static string GeneratePageSql(this DbConnection conn, string from, string select, string where
            , string pkField, string orderBy)
        {
            return GeneratePageSql(conn, from, select, where, new string[] { pkField }, orderBy);
        }

        /// <summary>
        /// 生成翻页sql脚本的format字符串，其中{0}为page，并且从1开始，{1}为size
        /// </summary>        
        /// <param name="from">sql的from部分，可使用Join</param>
        /// <param name="select">sql的select部分，如果使用了Join和别名，这里需要加上别名</param>
        /// <param name="where">sql的where部分，如果使用了Join和别名，这里需要加上别名</param>
        /// <param name="pkFields">sql的每条记录的标识主键，如果使用了Join和别名，这里需要加上别名</param>
        /// <param name="orderBy">sql的orderby部分，如果使用了Join和别名，这里需要加上别名</param>
        /// <returns></returns>
        public static string GeneratePageSql(this DbConnection conn, string from, string select, string where
            , IEnumerable<string> pkFields, string orderBy)
        {
            if (conn is SqlConnection)
            {
                StringBuilder sqlbuilder = new StringBuilder();
                sqlbuilder.AppendFormat("SELECT {0} FROM {1} JOIN (", select, from);
                sqlbuilder.Append("SELECT TOP ({0} * {1}) ");
                sqlbuilder.AppendFormat(" ROW_NUMBER() OVER (ORDER BY {0}) n, {1} FROM {2} {3}"
                    , orderBy
                    , string.Join(",", pkFields.Select(p => string.Format("{0} AS {0}JoinID", p)))
                    , from
                    , (string.IsNullOrEmpty(where) ? "" : "WHERE " + where));
                sqlbuilder.AppendFormat(") w2 ON {0} "
                    , string.Join(" AND ", pkFields.Select(p => string.Format("{0} = w2.{0}JoinID", p))));
                sqlbuilder.Append(" WHERE w2.n > ({0}-1) * {1} ORDER BY w2.n ASC;");
                return sqlbuilder.ToString();
            }

            throw new NotImplementedException();
            //if (conn is MySqlConnection)
            //{
            //    StringBuilder sqlbuilder = new StringBuilder();
            //    sqlbuilder.AppendFormat("SELECT {0} FROM {1}", select, from);
            //    sqlbuilder.AppendFormat(" {0}", (string.IsNullOrEmpty(where) ? "" : "WHERE " + where));
            //    sqlbuilder.AppendFormat(" {0}", orderBy);
            //    sqlbuilder.Append(" LIMIT ({0}-1)*{1}, {0}*{1};");
            //    return sqlbuilder.ToString();
            //}

            return string.Empty;
        }

        /// <summary>
        /// 生成翻页sql脚本的format字符串，其中{0}为page，并且从1开始，{1}为size
        /// </summary>        
        /// <param name="sql"></param>
        /// <param name="orderby">sql的orderby部分，如果使用了Join和别名，这里要去掉别名</param>
        /// <returns></returns>
        public static string GeneratePageSql(this DbConnection conn, string sql, string orderby)
        {
            if (conn is SqlConnection)
            {
                StringBuilder sqlbuilder = new StringBuilder();
                sqlbuilder.Append("SELECT TOP ({1}) * FROM (");
                sqlbuilder.AppendFormat("SELECT ROW_NUMBER() OVER ( ORDER BY {0}) AS n, b.* FROM ({1}) b", orderby, sql);
                sqlbuilder.Append(") a WHERE n > ({1} * ({0} - 1));");
                return sqlbuilder.ToString();
            }

            throw new NotImplementedException();
            //if (conn is MySqlConnection)
            //{
            //    StringBuilder sqlbuilder = new StringBuilder();
            //    sql = sql.Trim(new[] { ' ', ';' });
            //    sqlbuilder.Append(sql);
            //    if (!sql.Contains(" order by"))
            //    {                    
            //        sqlbuilder.AppendFormat(" {0}", orderby);
            //    }
            //    sqlbuilder.Append(" LIMIT ({0}-1)*{1}, {0}*{1};");
            //    return sqlbuilder.ToString();
            //}

            return string.Empty;
        }
        #endregion
    }
}
